﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace DatabaseClassFunnyHealthy.Business
{
    public class CategoryBusiness
    {
        public static bool InsertCategory(Category cate, int isSection)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "";
                SqlParameter[] pa;
                if (isSection == 1)
                {
                    sql = "insert into category(CategoryName,ParentCategoryID,Section,Priority,Visible,LinkSubject,ImagePath,Keyword,Description) values(@cateName,Null,@isSection,@priority,@isVisible,@link,@cateImage,@metakey,@description)";
                    pa = new SqlParameter[8];
                    pa[0] = new SqlParameter("@isSection", cate.Section);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.ImagePath);
                    pa[3] = new SqlParameter("@metakey", cate.Keyword);
                    pa[4] = new SqlParameter("@isVisible", cate.Visible);
                    pa[5] = new SqlParameter("@priority", cate.Priority);
                    pa[6] = new SqlParameter("@link", cate.Link);
                    pa[7] = new SqlParameter("@description", cate.Description);

                }
                else
                {
                    sql = "insert into category(CategoryName,ParentCategoryID,Section,Priority,Visible,LinkSubject,ImagePath,Keyword,Description) values(@cateName,@parentCateID,@isSection,@priority,@isVisible,@link,@cateImage,@metakey,@description)";
                    pa = new SqlParameter[9];
                    pa[0] = new SqlParameter("@isSection", cate.Section);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.ImagePath);
                    pa[3] = new SqlParameter("@metakey", cate.Keyword);
                    pa[4] = new SqlParameter("@isVisible", cate.Visible);
                    pa[5] = new SqlParameter("@priority", cate.Priority);
                    pa[6] = new SqlParameter("@parentCateID", cate.ParentCategoryID);
                    pa[7] = new SqlParameter("@link", cate.Link);
                    pa[8] = new SqlParameter("@description", cate.Description);
                }
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }

                return rs;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.CloseData();

            }
        }

        public static bool UpdateCategory(int cateID, Category cate, int isSection)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "";
                SqlParameter[] pa;
                if (isSection == 1)
                {
                    sql = "update category set ParentCategoryID=NULL,CategoryName=@cateName,ImagePath=@cateImage,Keyword=@metakey,Section=@isSection,LinkSubject=@link,Description=@description where ID=@cateID";
                    pa = new SqlParameter[7];
                    pa[0] = new SqlParameter("@cateID", cateID);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.ImagePath);
                    pa[3] = new SqlParameter("@metakey", cate.Keyword);
                    pa[4] = new SqlParameter("@isSection", isSection);
                    pa[5] = new SqlParameter("@link", cate.Link);
                    pa[6] = new SqlParameter("@description", cate.Description);
                }
                else
                {
                    sql = "update category set ParentCategoryID=@parentCateID,CategoryName=@cateName,ImagePath=@cateImage,Keyword=@metakey,Section=@isSection,LinkSubject=@link,Description=@description where ID=@cateID";
                    pa = new SqlParameter[8];
                    pa[0] = new SqlParameter("@cateID", cateID);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.ImagePath);
                    pa[3] = new SqlParameter("@metakey", cate.Keyword);
                    pa[4] = new SqlParameter("@parentCateID", cate.ParentCategoryID);
                    pa[5] = new SqlParameter("@isSection", isSection);
                    pa[6] = new SqlParameter("@link", cate.Link);
                    pa[7] = new SqlParameter("@description", cate.Description);
                }
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }

                return rs;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static bool UpdateVisibleCategory(int cateID, int visible)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "update category set Visible=@isVisible where ID=@cateID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@cateID", cateID);
                pa[1] = new SqlParameter("@isVisible", visible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static bool UpdatePriorityCategory(int cateID, int priority)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "update category set Priority=@priority where ID=@cateID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@cateID", cateID);
                pa[1] = new SqlParameter("@priority", priority);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static int GetCateIDByProduct(int subjectID)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                int cateID = 0;
                string sql = "select top 1 CategoryID from subjectcategỏy where SubjectID=@subjectID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@subjectID", subjectID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    cateID = int.Parse(reader["CategoryID"].ToString());
                }
                return cateID;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return 0;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static List<Category> ListAllCategory()
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.ID,cate.ParentCategoryID,cate.CategoryName,cate.CategoryName as ParentCategoryName, cate.Section,cate.LinkSubject,cate.ImagePath,cate.Keyword,cate.Visible,cate.Priority,cate.Description from category cate";
                SqlCommand command = helper.GetCommandNonParameter(sql, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.Description = reader["Description"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static List<Category> ListAllCategoryFollowParent(int parentCateID)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.ID,cate.ParentCategoryID,cate.CategoryName,cate.LinkSubject,cate.CategoryName as ParentCategoryName, cate.Section,cate.ImagePath,cate.Keyword,cate.Visible,cate.Priority,cate.Description from category cate where ParentCategoryID=@parentcateID order by Priority asc";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@parentcateID", parentCateID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.Description = reader["Description"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static List<Category> ListAllCategoryFollowParent(int parentCateID, int isVisible)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.ID,cate.ParentCategoryID,cate.CategoryName,cate.LinkSubject,cate.CategoryName as ParentCategoryName, cate.Section,cate.ImagePath,cate.Keyword,cate.Visible,cate.Priority,cate.Description from category cate where ParentCategoryID=@parentcateID and Visible=@isVisible order by Priority asc";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@parentcateID", parentCateID);
                pa[1] = new SqlParameter("@isVisible", isVisible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    cate.Description = reader["Description"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
               // Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static List<Category> ListAllCategoryFollowSection(int section)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.ID,cate.ParentCategoryID,cate.CategoryName,cate.LinkSubject,cate.CategoryName as ParentCategoryName, cate.Section,cate.ImagePath,cate.Meyword,cate.Visible,cate.Priority,cate.Description from category cate where Section=@section order by Priority asc";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@section", section);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    cate.Description = reader["Description"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
               // Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static List<Category> ListAllCategoryFollowSection(int section, int isVisible)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.ID,cate.ParentCategoryID,cate.CategoryName,cate.LinkSubject,cate.CategoryName as ParentCategoryName, cate.Section,cate.ImagePath,cate.Meyword,cate.Visible,cate.Priority,cate.Description from category cate where Section=@section and Visible=@visible order by Priority asc";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@section", section);
                pa[1] = new SqlParameter("@visible", isVisible);

                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    cate.Description = reader["Description"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static List<Category> ListAllCategoryVisible(int visible)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.ID,cate.ParentCategoryID,cate.CategoryName,cate.LinkSubject,cate.CategoryName as ParentCategoryName, cate.Section,cate.ImagePath,cate.Keyword,cate.Visible,cate.Priority,cate.Description from category cate,category parent where cate.Visible=@visible order by Priority asc";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@visible", visible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    cate.Description = reader["Description"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }


        public static List<Category> ListCategoryVisible(int visible)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select * from category cate where Visible=@visible";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@visible", visible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.Description = reader["Description"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
               //Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }


        public static Category GetCategoryDetail(int cateID)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                Category cate = null;
                string sql = "select *from category where CategoryID=@cateID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@cateID", cateID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    cate = new Category();
                    cate.ID = int.Parse(reader["ID"].ToString());
                    cate.ImagePath = reader["ImagePath"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.Section = int.Parse(reader["Section"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Visible = int.Parse(reader["Visible"].ToString());
                    cate.Keyword = reader["Keyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.Link = reader["LinkSubject"].ToString();
                    cate.Description = reader["Description"].ToString();
                }
                return cate;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static bool InsertCategorySubject(int cateID, int subjectID)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "insert into subjectcategory(CategoryID,SubjectID,Priority) values(@cateID,@subjectID,1)";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@subjectID", subjectID);
                pa[1] = new SqlParameter("@cateID", cateID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static bool DeleteCategorySubjectFollowSubject(int subjectID)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "delete subjectcategory where SubjectID=@subjectID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@subjectID", subjectID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
               // Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.CloseData();
            }
        }

        public static bool DeleteCategoryProductFollowCategoryID(int cateID)
        {
            ClassDatabase helper = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "delete subjectcategory where CategoryID=@cateID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@cateID", cateID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                //Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.CloseData();
            }
        }
    }
}
